package com.lz.repository.ly.impl;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.lz.entity.Bank;
import com.lz.entity.Employee;
import com.lz.entity.MortgageSheet;
import com.lz.repository.ly.MortgageSheetRepository;
import com.lz.vo.ly.SelectMortgageSheetVO;

@Repository
public class MortgageSheetRepositoryImpl implements MortgageSheetRepository {

	@Autowired
	private SessionFactory sessionFactory;
	
	private Session getSession() {
		return sessionFactory.getCurrentSession();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<SelectMortgageSheetVO> getMortgageAll(Integer pageNo,Integer pageSize,MortgageSheet mortgageSheet,
			String start_time,String end_time) {
		
		
		StringBuffer sql = new StringBuffer("select new com.lz.vo.ly.SelectMortgageSheetVO(ms,cu,ey,ca,bk) from MortgageSheet ms,Car ca,Customer cu,Employee ey ,Bank bk "
								+ "where ms.car.carId=ca.carId and ms.employee.employeeId=ey.employeeId and ms.customer.customerId=cu.customerId "
								+ "and ms.bank.bankId=bk.bankId");
		
		if(mortgageSheet.getMortgageSheetNo() != null) {
			sql.append(" and ms.mortgageSheetNo like '%"+mortgageSheet.getMortgageSheetNo()+"%'");
		}
		if(mortgageSheet.getCar() != null && mortgageSheet.getCar().getVin() != null && !"".equals(mortgageSheet.getCar().getVin()) ) {
			sql.append(" and ca.vin like '%"+mortgageSheet.getCar().getVin()+"%'");
		}
		if(mortgageSheet.getCustomer() != null && mortgageSheet.getCustomer().getCustomerId() != null) {
			sql.append(" and cu.customerId like '%"+mortgageSheet.getCustomer().getCustomerId()+"%'");
		}
		if(mortgageSheet.getCustomer() != null && mortgageSheet.getCustomer().getCustomerName() != null && !"".equals(mortgageSheet.getCustomer().getCustomerName())) {
			sql.append(" and cu.customerName like '%"+mortgageSheet.getCustomer().getCustomerName()+"%'");
		}
		if(mortgageSheet.getOrderId() != null) {
			sql.append(" and ms.orderId like '%"+mortgageSheet.getOrderId()+"%'");
		}
		if(mortgageSheet.getBank()!=null && mortgageSheet.getBank().getBankName() !=null && !"-1".equals(mortgageSheet.getBank().getBankName())) {
			sql.append(" and bk.bankName='"+mortgageSheet.getBank().getBankName()+"'");
		}
		if(mortgageSheet.getEmployee() != null && mortgageSheet.getEmployee().getEmployeeName() != null && !"-1".equals(mortgageSheet.getEmployee().getEmployeeName())) {
			sql.append(" and ms.employee.employeeName='"+mortgageSheet.getEmployee().getEmployeeName()+"'");
		}
		if(mortgageSheet.getMortgageSheetState() != null && !"-1".equals(mortgageSheet.getMortgageSheetState()) && !"".equals(mortgageSheet.getMortgageSheetState())) {
			sql.append(" and ms.mortgageSheetState='"+mortgageSheet.getMortgageSheetState()+"'");
		}
		if((start_time != null && !"".equals(start_time))) {
			sql.append(" and ms.mortgageSheetDate >= '"+start_time+"'");
		}
		if(end_time != null && !"".equals(end_time)) {
			sql.append(" and ms.mortgageSheetDate <= '"+end_time+"'");
		}
		
		Query query = getSession().createQuery(sql.toString());
		query.setFirstResult(pageNo);
		query.setMaxResults(pageSize);
		return query.list();
	}

	@Override
	public MortgageSheet getMortgageById(Integer id) {
		String hql = "select sheet from MortgageSheet sheet where sheet.mortgageSheetNo=:var1";
		 MortgageSheet mg = (MortgageSheet)getSession().createQuery(hql).setInteger("var1", id).uniqueResult();
		 getSession().close();
		return mg;
	}
	
	public Integer updMortgage(MortgageSheet mortgageSheet) {
		String hql = "update MortgageSheet m set m.mortgageSheetState=:var1 where m.mortgageSheetNo=:var2";
		Session session = getSession();
		Query query = session.createQuery(hql);
		query.setString("var1", mortgageSheet.getMortgageSheetState());
		query.setInteger("var2", mortgageSheet.getMortgageSheetNo());
		int i = query.executeUpdate();
		return i;
			
	}

	/**
	 * 查询总条数
	 */
	@Override
	public Integer count(MortgageSheet mortgageSheet,String start_time,String end_time) {
	
		StringBuffer sql = new StringBuffer("select count(1) from MortgageSheet ms,Car ca,Customer cu,Employee ey ,Bank bk "
				+ "where ms.car.carId=ca.carId and ms.employee.employeeId=ey.employeeId and ms.customer.customerId=cu.customerId "
				+ "and ms.bank.bankId=bk.bankId");
		
		if(mortgageSheet.getMortgageSheetNo() != null) {
			sql.append(" and ms.mortgageSheetNo like '%"+mortgageSheet.getMortgageSheetNo()+"%'");
		}
		if(mortgageSheet.getCar() != null && mortgageSheet.getCar().getVin() != null && !"".equals(mortgageSheet.getCar().getVin()) ) {
			sql.append(" and ca.vin like '%"+mortgageSheet.getCar().getVin()+"%'");
		}
		if(mortgageSheet.getCustomer() != null && mortgageSheet.getCustomer().getCustomerId() != null) {
			sql.append(" and cu.customerId like '%"+mortgageSheet.getCustomer().getCustomerId()+"%'");
		}
		if(mortgageSheet.getCustomer() != null && mortgageSheet.getCustomer().getCustomerName() != null && !"".equals(mortgageSheet.getCustomer().getCustomerName())) {
			sql.append(" and cu.customerName like '%"+mortgageSheet.getCustomer().getCustomerName()+"%'");
		}
		if(mortgageSheet.getOrderId() != null) {
			sql.append(" and ms.orderId like '%"+mortgageSheet.getOrderId()+"%'");
		}
		if(mortgageSheet.getBank()!=null && mortgageSheet.getBank().getBankName() !=null && !"-1".equals(mortgageSheet.getBank().getBankName())) {
			sql.append(" and bk.bankName='"+mortgageSheet.getBank().getBankName()+"'");
		}
		if(mortgageSheet.getEmployee() != null && mortgageSheet.getEmployee().getEmployeeName() != null && !"-1".equals(mortgageSheet.getEmployee().getEmployeeName())) {
			sql.append(" and ms.employee.employeeName='"+mortgageSheet.getEmployee().getEmployeeName()+"'");
		}
		if(mortgageSheet.getMortgageSheetState() != null && !"-1".equals(mortgageSheet.getMortgageSheetState()) && !"".equals(mortgageSheet.getMortgageSheetState()) ) {
			sql.append(" and ms.mortgageSheetState='"+mortgageSheet.getMortgageSheetState()+"'");
		}
		if((start_time != null && !"".equals(start_time))) {
			sql.append(" and ms.mortgageSheetDate >= '"+start_time+"'");
		}
		if(end_time != null && !"".equals(end_time)) {
			sql.append(" and ms.mortgageSheetDate <= '"+end_time+"'");
		}
		
		Query query = getSession().createQuery(sql.toString());
		
		return ((Long) query.uniqueResult()).intValue();
	}

	/**
	 * 查询金融机构
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Bank> getBankList() {
		String hql = "from Bank";
		
		return getSession().createQuery(hql).list();
	}

	/**
	 * 查询开单人
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Employee> getEmployeeList() {
		String hql = "from Employee";
		
		return getSession().createQuery(hql).list();
	}

}
